﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Linq.Expressions;
using System.Collections;

namespace HRdex_Final.Forms
{
    public partial class WebForm3 : System.Web.UI.Page
    {
        SqlConnection conn = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\HRdexDB.mdf;Integrated Security=True;User Instance=True");

        string SessionID()
        {
            return Session["EmployeeID"].ToString();
        }

        protected void Page_Load(object sender, EventArgs e)
        {
 

            SqlCommand cmd = new SqlCommand("SELECT EmployeeID, LastName + ', ' + FirstName + ' ' + MiddleName AS Name, DeptID, catPositionsID, CollegeID FROM formEmployees WHERE EmployeeID="+SessionID(), conn);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable("dt");

            da.Fill(dt);

            txtEmpNo.Text = dt.Rows[0].ItemArray[0].ToString();
            txtEmpName.Text = dt.Rows[0].ItemArray[1].ToString();
            ddlDept.SelectedValue = dt.Rows[0].ItemArray[2].ToString();
            ddlPosition.SelectedValue = dt.Rows[0].ItemArray[3].ToString();
            ddlCol.SelectedValue = dt.Rows[0].ItemArray[4].ToString();
            txtEmpName.Enabled = false;
            txtEmpNo.Enabled = false;
            ddlDept.Enabled = false;
            ddlPosition.Enabled = false;
            ddlCol.Enabled = false;
            ddlApprovalHead.Visible = false;
            ddlApprovalDean.Visible = false;
            ddlApprovalHR.Visible = false;
            txtLeaveID.Visible = false;
            Label4.Visible = false;
            lblApprovalChair.Visible = false;
            lblApprovalDean.Visible = false;
            lblApprovalHR.Visible = false;
            txtAppHRleave.Visible = false;
            txtAppChairleave.Visible = false;
            txtAppDeanleave.Visible = false;
            Label5.Visible = false;
            CheckBoxList1.Visible = false;

            Select();

            if (ddlLeave.SelectedValue == "1")
            {
                if (Convert.ToInt16(txtTotalDays.Text) <= 3)
                {
                    Label5.Text = "You cannot leave morethan 3 days for Vacation Leave";
                }
            }
            else if (ddlLeave.SelectedValue == "4")
            {
                if (Convert.ToInt16(txtTotalDays.Text) <= 1)
                {
                    Label5.Text = "You cannot leave morethan 1 days for Birthday Leave";
                }
            }
            else if (ddlLeave.SelectedValue == "2")
            {
                if (Convert.ToInt16(txtTotalDays.Text) <= 5)
                {
                    Label5.Text = "You cannot leave morethan 5 days for Birthday Leave";
                }
            }
            else if (ddlLeave.SelectedValue == "3")
            {
                if (Convert.ToInt16(txtTotalDays.Text) <= 7)
                {
                    Label5.Text = "You cannot leave morethan 7 days for Birthday Leave";
                }
            }
        }

        protected void btnSave_Click(object sender, EventArgs e)
        {
            string LeaveID = Request.QueryString["LeaveID"];
            if (txtLeaveID.Text != LeaveID)
            {


                SqlDataAdapter adapter = new SqlDataAdapter();
                DataSet ds = new DataSet();

                conn.Open();

                SqlCommand cmd = new SqlCommand("Insert Into formLeave (EmployeeID, PositionID, DepartmentID, CollegeID, CatLeaveID, fromDateTime, toDateTime, Reason, LeaveAddress, totalDaysLeave) " +
                    "Values (@EmpID, @posID, @deptID, @colID,@catLeaveID,@fdatetime, @tdatetime, @reason, @leaveAdd, @totalDaysLeave )", conn);

                cmd.Parameters.Add("@EmpID", SqlDbType.VarChar).Value = txtEmpNo.Text;
                cmd.Parameters.Add("@posID", SqlDbType.VarChar).Value = ddlPosition.SelectedValue;
                cmd.Parameters.Add("@deptID", SqlDbType.VarChar).Value = ddlDept.SelectedValue;
                cmd.Parameters.Add("@colID", SqlDbType.VarChar).Value = ddlCol.SelectedValue;
                cmd.Parameters.Add("@catLeaveID", SqlDbType.VarChar).Value = ddlLeave.Text;
                cmd.Parameters.Add("@fdatetime", SqlDbType.VarChar).Value = txtFrom.Text+ " - " + ddl0Hour.Text + ":" + ddl0Min.Text;
                cmd.Parameters.Add("@tdatetime", SqlDbType.VarChar).Value = txtTo.Text + " - " + ddl1Hour.Text + ":" + ddl1Min.Text;
                cmd.Parameters.Add("@reason", SqlDbType.VarChar).Value = txtReason.Text;
                cmd.Parameters.Add("@leaveAdd", SqlDbType.VarChar).Value = txtAddress.Text;
                                //DATE TIME COMPUTATION
                DateTime dtF = Convert.ToDateTime(txtFrom.Text);
                DateTime dtT = Convert.ToDateTime(txtTo.Text);
                TimeSpan ts = dtT.Subtract(dtF);
                txtTotalDays.Text = Convert.ToString(ts.Days);
                cmd.Parameters.Add("@totalDaysLeave", SqlDbType.VarChar).Value = txtTotalDays.Text;

                cmd.ExecuteNonQuery();

                conn.Close();
                lblConfirmationMessage.Visible = true;
                lblConfirmationMessage.Text = "You successfully add leave request. Please wait for the confirmation upon approval.";
                
                //notification
                //submit_notify(slct_head(slct_dept(SessionID())),SessionID());
            }
            else
            {
                Edit();
                //notification();
            }

            txtAddress.Text = "";
            txtFrom.Text = "";
            txtTo.Text = "";
            txtTotalDays.Text = "";
            txtReason.Text = "";

            
        }

        public void Select()
        {
            string LeaveID = Request.QueryString["LeaveID"];


            if (LeaveID != null)
            {
                txtAddress.Enabled = false;
                txtEmpName.Enabled = false;
                txtEmpNo.Enabled = false;
                txtFrom.Enabled = false;
                txtLeaveID.Enabled = false;
                txtReason.Enabled = false;
                txtTo.Enabled = false;
                txtTotalDays.Enabled = false;
                ddlCol.Enabled = false;
                ddlDept.Enabled = false;
                ddlLeave.Enabled = false;
                ddlPosition.Enabled = false;
                btnClear.Visible = false;
                txtLeaveID.Visible = true;
                Label4.Visible = true;
                ddlDaysMonthsYear.Enabled = false;
                ddl0Hour.Enabled = false;
                ddl0Min.Enabled = false;
                ddl1Hour.Enabled = false;
                ddl1Min.Enabled = false;

                txtAppHRleave.Visible = true;
                txtAppHRleave.Enabled = false;
                txtAppChairleave.Visible = true;
                txtAppChairleave.Enabled = false;
                txtAppDeanleave.Visible = true;
                txtAppDeanleave.Enabled = false;
                SqlCommand cmd2 = new SqlCommand("SELECT formLeave.LeaveID, formLeave.CatLeaveID, formLeave.fromDateTime, formLeave.toDateTime, formLeave.totalDaysLeave, formLeave.Reason, formLeave.LeaveAddress, formLeave.EmployeeID, formEmployees.LastName + ', ' + formEmployees.FirstName + ' ' + formEmployees.MiddleName AS Name, formLeave.DepartmentID, formLeave.PositionID, formLeave.CollegeID, formLeave.HeadApproval, formLeave.DeanApproval, formLeave.HRApproval FROM formLeave INNER JOIN formEmployees ON formLeave.EmployeeID = formEmployees.EmployeeID" +
                    " WHERE (formLeave.LeaveID=" + LeaveID + ")", conn);
                SqlDataAdapter da2 = new SqlDataAdapter(cmd2);
                DataTable dt2 = new DataTable("dt2");

                da2.Fill(dt2);

                txtLeaveID.Text = dt2.Rows[0].ItemArray[0].ToString();
                ddlLeave.SelectedValue = dt2.Rows[0].ItemArray[1].ToString();
                txtFrom.Text = dt2.Rows[0].ItemArray[2].ToString();
                txtTo.Text = dt2.Rows[0].ItemArray[3].ToString();
                txtTotalDays.Text = dt2.Rows[0].ItemArray[4].ToString();
                txtReason.Text = dt2.Rows[0].ItemArray[5].ToString();
                txtAddress.Text = dt2.Rows[0].ItemArray[6].ToString();
                txtEmpNo.Text = dt2.Rows[0].ItemArray[7].ToString();
                txtEmpName.Text = dt2.Rows[0].ItemArray[8].ToString();
                ddlDept.SelectedValue = dt2.Rows[0].ItemArray[9].ToString();
                ddlPosition.SelectedValue = dt2.Rows[0].ItemArray[10].ToString();
                ddlCol.SelectedValue = dt2.Rows[0].ItemArray[11].ToString();

                lblApprovalChair.Visible = true;
                lblApprovalDean.Visible = true;
                lblApprovalHR.Visible = true;
                txtAppChairleave.Text = dt2.Rows[0].ItemArray[12].ToString();
                txtAppDeanleave.Text = dt2.Rows[0].ItemArray[13].ToString();
                txtAppHRleave.Text = dt2.Rows[0].ItemArray[14].ToString();
                btnSave.Visible = false;
                #region ApprovedDenied

                SqlCommand cmd31 = new SqlCommand("SELECT formEmployees.EmployeeID, catUserLevel.catUserLevelName AS [User Level], catPositions.catPositionsName AS Position, catDepartment.DepartmentName AS Department, catCollege.CollegeName AS Office FROM formEmployees INNER JOIN catDepartment ON formEmployees.DeptID = catDepartment.DeptID INNER JOIN catCollege ON formEmployees.CollegeID = catCollege.CollegeID INNER JOIN catPositions ON formEmployees.catPositionsID = catPositions.catPositionsID INNER JOIN catUserLevel ON formEmployees.UserLevelID = catUserLevel.UserLevelID WHERE (EmployeeID = '" + SessionID() + "')", conn);
                SqlDataAdapter da31 = new SqlDataAdapter(cmd31);
                DataTable dt31 = new DataTable("dt31");
                da31.Fill(dt31);


                if (dt31.Rows[0].ItemArray[1].ToString() == "Admin" || dt31.Rows[0].ItemArray[1].ToString() == "SubAdmin")
                {
                    if (dt31.Rows[0].ItemArray[4].ToString() == "Human Resource Management and Development" && dt31.Rows[0].ItemArray[3].ToString() == "HR Directors Office" && dt31.Rows[0].ItemArray[1].ToString() == "Admin" && dt31.Rows[0].ItemArray[2].ToString() == "Director" || dt31.Rows[0].ItemArray[4].ToString() == "Adamson Office" && dt31.Rows[0].ItemArray[3].ToString() == "HR Salaries and Benefits" && dt31.Rows[0].ItemArray[1].ToString() == "SubAdmin" && dt31.Rows[0].ItemArray[2].ToString() == "Head")
                    {
                        txtAppDeanleave.Visible = true;
                        txtAppHRleave.Visible = false;
                        txtAppChairleave.Visible = true;
                        ddlApprovalHead.Visible = false;
                        ddlApprovalDean.Visible = false;
                        ddlApprovalHR.Visible = true;
                        btnSave.Visible = true; 
                        if (dt2.Rows[0].ItemArray[13].ToString() == "Approved" && dt2.Rows[0].ItemArray[14].ToString() != "Denied" && dt2.Rows[0].ItemArray[14].ToString() != "Approved")
                        {
                    
                            ddlApprovalDean.Enabled = false;
                            ddlApprovalHead.Enabled = false;
                            ddlApprovalHR.Enabled = true;
                        }
                        else
                        {
                            ddlApprovalDean.Visible = false;
                            ddlApprovalHead.Visible = false;
                            ddlApprovalHR.Visible = false;
                            txtAppDeanleave.Visible = true;
                            txtAppHRleave.Visible = true;
                            txtAppChairleave.Visible = true;
                        }
                    }
                }
                //Head Approval

                else if (dt31.Rows[0].ItemArray[1].ToString() == "Faculty" || dt31.Rows[0].ItemArray[1].ToString() == "Employee")
                {
                    if (dt31.Rows[0].ItemArray[2].ToString() == "Chairperson" || dt31.Rows[0].ItemArray[2].ToString() == "Head" || dt31.Rows[0].ItemArray[2].ToString() == "Supervisor")
                    {

                        ddlApprovalHead.Visible = true;
                        ddlApprovalDean.Visible = true;
                        ddlApprovalHR.Visible = true;
                        btnSave.Visible = true; 
                        if (dt2.Rows[0].ItemArray[12].ToString() != "Denied" && dt2.Rows[0].ItemArray[12].ToString() != "Approved")
                        {
                            ddlApprovalDean.Enabled = false;
                            ddlApprovalHead.Enabled = true;
                            ddlApprovalHR.Enabled = false;
                        }
                        else
                        {
                            txtAppDeanleave.Visible = true;
                            txtAppHRleave.Visible = true;
                            txtAppChairleave.Visible = true;
                            ddlApprovalDean.Visible = false;
                            ddlApprovalHead.Visible = false;
                            ddlApprovalHR.Visible = false;
                        }
                    }

                    else if (dt31.Rows[0].ItemArray[2].ToString() == "Dean" || dt31.Rows[0].ItemArray[2].ToString() == "Director")
                    {
                        txtAppDeanleave.Visible = false;
                        txtAppHRleave.Visible = false;
                        txtAppChairleave.Visible = true;
                        ddlApprovalHead.Visible = false;
                        ddlApprovalDean.Visible = true;
                        ddlApprovalHR.Visible = true;
                        btnSave.Visible = true;
                        if (dt2.Rows[0].ItemArray[12].ToString() == "Approved" && dt2.Rows[0].ItemArray[13].ToString() != "Denied" && dt2.Rows[0].ItemArray[13].ToString() != "Approved")
                        {
                           
                            ddlApprovalDean.Enabled = true;
                            ddlApprovalHead.Enabled = false;
                            ddlApprovalHR.Enabled = false;
                        }
                        else
                        {
                            txtAppDeanleave.Visible = true;
                            txtAppHRleave.Visible = true;
                            txtAppChairleave.Visible = true;
                            ddlApprovalDean.Visible = false;
                            ddlApprovalHead.Visible = false;
                            ddlApprovalHR.Visible = false;
                        }
                    }
                }
                else
                {
                    ddlApprovalHead.Visible = true;
                    ddlApprovalDean.Visible = true;
                    ddlApprovalHR.Visible = true;
                }
                #endregion
            }
        }


        public void Edit()
        {
            string LeaveID = Request.QueryString["LeaveID"];
            //System Head Approved
            if (ddlApprovalHead.Text == "Approved" || ddlApprovalHead.Text == "Denied")
            {
                SqlCommand cmdx = new SqlCommand("UPDATE formLeave SET  HeadApproval ='" + ddlApprovalHead.Text + "' WHERE (LeaveID = " + LeaveID + ")", conn);
                SqlDataAdapter dax = new SqlDataAdapter(cmdx);
                conn.Open();
                cmdx.ExecuteNonQuery();
                conn.Close();
                lblConfirmationMessage.Visible = true;
                lblConfirmationMessage.Text = "You successfully give your approval status..";

                //Notifiction
                notification(ddlApprovalHead.Text, 1);
            }
            //System Dean Approval

            if (ddlApprovalDean.Text == "Approved" || ddlApprovalDean.Text == "Denied")
            {
                SqlCommand cmd22 = new SqlCommand("UPDATE formLeave SET  DeanApproval ='" + ddlApprovalDean.Text + "' WHERE (LeaveID = " + LeaveID + ")", conn);
                SqlDataAdapter da22 = new SqlDataAdapter(cmd22);
                conn.Open();
                cmd22.ExecuteNonQuery();
                conn.Close();
                lblConfirmationMessage.Visible = true;
                lblConfirmationMessage.Text = "You successfully give your approval status..";

                //Notification
                notification(ddlApprovalDean.Text, 2);

            }
            //System Dean Approval

            else if (ddlApprovalHR.Text == "Approved" || ddlApprovalHR.Text == "Denied")
            {
                SqlCommand cmd33 = new SqlCommand("UPDATE formLeave SET  HRApproval ='" + ddlApprovalHR.Text + "' WHERE (LeaveID = " + LeaveID + ")", conn);
                SqlDataAdapter da33 = new SqlDataAdapter(cmd33);
                conn.Open();
                cmd33.ExecuteNonQuery();
                conn.Close();
                lblConfirmationMessage.Visible = true;
                lblConfirmationMessage.Text = "You successfully give your approval status..";

                //Notification
                notification(ddlApprovalHR.Text, 0);
            }
        }

        void notification(string result, int userlvl)
        {
            string LeaveID = Request.QueryString["LeaveID"];
            string desc;

            switch (userlvl)
            {
                case 1:
                    desc = "Your Application has been " + result + " by the HR Director";
                    break;
                case 2:
                    desc = "Your Application has been " + result + " Dean/Director";
                    break;
                default:
                    desc = "Your Application has been " + result + " by the Dept. Head/Supervisor";
                    break;
            }


            string sqlc = "INSERT INTO formMessegeSender " +
                "(Subject, Description, DateTime, EmployeeID, catMessegeID, fromEmpID, Readed) " +
                "VALUES ('Flexiform Request','" +
                desc + "','" +
                DateTime.Now + "'," +
                cbasender(LeaveID) +
                ",2," +
                SessionID() +
                ", 0)";
            SqlCommand cmd = new SqlCommand(sqlc, conn);

            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
        }

        string cbasender(string src)
        {
            string sqlx = "SELECT EmployeeID FROM formLeave WHERE LeaveID = " + src;
            SqlDataAdapter cmd = new SqlDataAdapter(sqlx, conn);
            DataTable tbl = new DataTable();

            conn.Open();
            cmd.Fill(tbl);
            conn.Close();

            return tbl.Rows[0][0].ToString();
        }

        protected void btnClear_Click(object sender, EventArgs e)
        {
            txtAddress.Text = null;
            txtEmpName.Text = null;
            txtEmpNo.Text = null;
            txtFrom.Text = null;
            txtLeaveID.Text = null;
            txtReason.Text = null;
            txtTo.Text = null;
            txtTotalDays.Text = null;
        }
        void DateTimeLEave()
        {
            DateTime dt = DateTime.Now;
            int day = dt.Day;

            if (day == 1)
            {
                int TotalDays = 12;
            }
        }
    }
}
